package com.example.bookshop.dao;

import com.example.bookshop.bean.BookBean;
import com.example.bookshop.bean.PageBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Component
public class BookDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 插入图书
     *
     * @param book 书
     * @return int
     */
    public int insertBook(BookBean book){
        String sql = "INSERT INTO book (book_name, type_id,author, publish, depict, price, storages) VALUES (?,?,?,?,?,?,?)";
        return jdbcTemplate.update(sql,book.getBookName(),
                book.getTypeId(),book.getAuthor(),
                book.getPublish(),book.getDepict(),
                book.getPrice(),book.getStorages());
    }

    /**
     * 查询所有书信息和分页信息
     *
     * @return {@link List}<{@link BookBean}>
     */
    public List<BookBean> selectBookPage(PageBean page){
        String sql = "select book_id,book_name,book.type_id,type_name,author,publish,depict,price,storages" +
                " from book left join book_type bt on book.type_id = bt.type_id limit ?,?";
        List<BookBean> books= new ArrayList<>();
        books= jdbcTemplate.query(sql, new RowMapper<BookBean>() {
            @Override
            public BookBean mapRow(ResultSet rs, int rowNum) throws SQLException {
                BookBean book = new BookBean();
                book.setBookId(rs.getInt("book_id"));
                book.setBookName(rs.getString("book_name"));
                book.setTypeId(rs.getInt("type_id"));
                book.setTypeName(rs.getString("type_name"));
                book.setAuthor(rs.getString("author"));
                book.setPublish(rs.getString("publish"));
                book.setDepict(rs.getString("depict"));
                book.setPrice(rs.getDouble("price"));
                book.setStorages(rs.getInt("storages"));
                return book;
            }
        },(page.getPageNow()-1)*page.getPageSize(),page.getPageSize());
        return books;
    }

    /**
     * 查询总行数
     * @return
     */
    public int getRowCount(){
        String sql="SELECT COUNT(book_id) FROM book";
        return jdbcTemplate.queryForObject(sql,Integer.class);
    }

    /**
     * 删除书
     *
     * @param bookId 书id
     * @return int
     */
    public int deleteBook(Integer bookId){
        String sql="delete from book where book_id=?";
        return jdbcTemplate.update(sql,bookId);
    }

    /**
     * 更新书
     *
     * @param book 书
     * @return int
     */
    public int updateBook(BookBean book){
        String sql = "update book set book_name=?,type_id=?,author=?,publish=?,price=?,storages=?,depict=? where book_id=?";
        return jdbcTemplate.update(sql,book.getBookName(),book.getTypeId(),book.getAuthor(),book.getPublish(),
                                    book.getPrice(),book.getStorages(),book.getPrice(),book.getBookId());
    }
}
